package net.huashitong.Project.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.Project.entity.NewProjects;
import net.huashitong.Project.filter.NewProjectsFilter;
import net.huashitong.user.entity.BnpUser;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

@Repository
public class NewProjectsDao extends StringDao<NewProjects> {

    public List<NewProjects> queryByfilter(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (ObjectUtils.isNotBlank(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name",
                    StringUtils.getLikewords(filter.getName()));
        }
        if (ObjectUtils.isNotBlank(filter.getCreateDate())) {
            query.append(" and obj.createDate like:createDate").setParam("createDate",
                    StringUtils.getLikewords(filter.getCreateDate()));
        }
        if (ObjectUtils.isNotBlank(filter.getProjectType())) {
            //2001:计划新开工        2002:结转项目
            query.append(" and obj.projectType=:projectType").setParam("projectType", filter.getProjectType());
        }
        if (ObjectUtils.isNotBlank(filter.getUserId())) {
            query.append(" and obj.userId=:userId").setParam("userId", filter.getUserId());
        }
        if (ObjectUtils.isNotBlank(filter.getStatus())) {
            query.append(" and obj.status=:status").setParam("status", filter.getStatus());
        }
        if (ObjectUtils.isNotBlank(filter.getOwnerUnitPersonPhone())) {
            query.append(" and obj.ownerUnitPersonPhone =:ownerUnitPersonPhone ").setParam("ownerUnitPersonPhone", filter.getOwnerUnitPersonPhone());
        }
        if (ObjectUtils.isNotBlank(filter.getContactPhone())) {
            query.append(" and obj.contactPhone =:contactPhone ").setParam("contactPhone", filter.getContactPhone());
        }
        if (StringUtils.isNotBlank(filter.getResPhone())) {
            query.append(" and obj.resPhone =:resPhone ").setParam("resPhone", filter.getResPhone());
        }
        return query.listResult();
    }


    public List<NewProjects> getProjectInfo(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (filter.getName() != null && !"".equals(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (filter.getResDept() != null && !"".equals(filter.getResDept())) {
            query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
        }
        if (filter.getResUser() != null && !"".equals(filter.getResUser())) {
            query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
        }
        if (StringUtils.isNotBlank(filter.getResPhone())) {
            query.append(" and obj.resPhone =:resPhone").setParam("resPhone", filter.getResPhone());
        }
        if (filter.getNatureType() != null && !"".equals(filter.getNatureType())) {
            query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
        }
        if (filter.getMoney() != null && !"".equals(filter.getMoney())) {
            query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
        }
        //未开工项目包含暂停项目等（这里的意思就是除了开工就是未开工）
        if ("000".equals(filter.getStatus())) {
            query.append(" and obj.status !=:status").setParam("status", "4");
        } else {
            if (filter.getStatus() != null && !"".equals(filter.getStatus())) {
                query.append(" and obj.status =:status").setParam("status", filter.getStatus());
            }
        }

        if (filter.getAddress() != null && !"".equals(filter.getAddress())) {
            query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
        }
        if (filter.getDescribes() != null && !"".equals(filter.getDescribes())) {
            query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
        }
        if (filter.getContactName() != null && !"".equals(filter.getContactName())) {
            query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
        }
        if (filter.getContactPhone() != null && !"".equals(filter.getContactPhone())) {
            query.append(" and obj.contactPhone like:contactPhone").setParam("contactPhone", "%" + filter.getContactPhone() + "%");
        }
        if (filter.getIndustryType() != null && !"".equals(filter.getIndustryType())) {
            query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
        }
        if (filter.getProjectType() != null && !"".equals(filter.getProjectType())) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
        }
        if (filter.getSchedCode() != null && !"".equals(filter.getSchedCode())) {
            query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
        }
        if (filter.getSysId() != null && !"".equals(filter.getSysId())) {
            query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
        }
        if (filter.getUserId() != null && !"".equals(filter.getUserId())) {
            query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
        }
        if (filter.getOccupyResUser() != null && !"".equals(filter.getOccupyResUser())) {
            query.append(" and obj.occupyResUser =:occupyResUser").setParam("occupyResUser", filter.getOccupyResUser());
        }
        if (filter.getOccupyOperateUser() != null && !"".equals(filter.getOccupyOperateUser())) {
            query.append(" and obj.occupyOperateUser =:occupyOperateUser").setParam("occupyOperateUser", filter.getOccupyOperateUser());
        }
        if (filter.getOccupyDept() != null && !"".equals(filter.getOccupyDept())) {
            query.append(" and obj.occupyDept =:occupyDept").setParam("occupyDept", filter.getOccupyDept());
        }
        return query.listResult(filter);
    }

    public List<NewProjects> getProjectInfo3(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (filter.getName() != null && !"".equals(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (filter.getResDept() != null && !"".equals(filter.getResDept())) {
            query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
        }
        if (filter.getResUser() != null && !"".equals(filter.getResUser())) {
            query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
        }
        if (filter.getNatureType() != null && !"".equals(filter.getNatureType())) {
            query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
        }
        if (filter.getMoney() != null && !"".equals(filter.getMoney())) {
            query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
        }
        if (filter.getStatus() != null && !"".equals(filter.getStatus())) {
            query.append(" and obj.status =:status").setParam("status", filter.getStatus());
        }
        if (filter.getAddress() != null && !"".equals(filter.getAddress())) {
            query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
        }
        if (filter.getDescribes() != null && !"".equals(filter.getDescribes())) {
            query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
        }
        if (filter.getContactName() != null && !"".equals(filter.getContactName())) {
            query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
        }
        if (filter.getContactPhone() != null && !"".equals(filter.getContactPhone())) {
            query.append(" and obj.contactPhone like:contactPhone").setParam("contactPhone", "%" + filter.getContactPhone() + "%");
        }
        if (filter.getIndustryType() != null && !"".equals(filter.getIndustryType())) {
            query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
        }
        if (filter.getProjectType() != null && !"".equals(filter.getProjectType())) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
        }
        if (filter.getSchedCode() != null && !"".equals(filter.getSchedCode())) {
            query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
        }
        if (filter.getSysId() != null && !"".equals(filter.getSysId())) {
            query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
        }
        if (filter.getUserId() != null && !"".equals(filter.getUserId())) {
            query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
        }
        query.append(" order by obj.sysId DESC");

        return query.listResult();
    }

    /**
     * 根据id返回项目信息
     *
     * @param id
     * @return
     */
    public NewProjects getProjectById(String id) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.sysId =:sysId").setParam("sysId", id);
        return query.singleResult();
    }

    public int getProjectSum(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (ObjectUtils.isNotBlank(filter)) {
            if (filter.getName() != null && !"".equals(filter.getName())) {
                query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
            }
            if (filter.getResDept() != null && !"".equals(filter.getResDept())) {
                query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
            }
            if (filter.getResUser() != null && !"".equals(filter.getResUser())) {
                query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
            }
            if (filter.getNatureType() != null && !"".equals(filter.getNatureType())) {
                query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
            }
            if (filter.getMoney() != null && !"".equals(filter.getMoney())) {
                query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
            }
            if (filter.getStatus() != null && !"".equals(filter.getStatus())) {
                query.append(" and obj.status =:status").setParam("status", filter.getStatus());
            }
            if (filter.getAddress() != null && !"".equals(filter.getAddress())) {
                query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
            }
            if (filter.getDescribes() != null && !"".equals(filter.getDescribes())) {
                query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
            }
            if (filter.getContactName() != null && !"".equals(filter.getContactName())) {
                query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
            }
            if (filter.getContactPhone() != null && !"".equals(filter.getContactPhone())) {
                query.append(" and obj.contactPhone like:contactPhone").setParam("contactPhone", "%" + filter.getContactPhone() + "%");
            }
            if (filter.getIndustryType() != null && !"".equals(filter.getIndustryType())) {
                query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
            }
            if (filter.getProjectType() != null && !"".equals(filter.getProjectType())) {
                query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
            }
            if (filter.getSchedCode() != null && !"".equals(filter.getSchedCode())) {
                query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
            }
            if (filter.getSysId() != null && !"".equals(filter.getSysId())) {
                query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
            }
            if (filter.getUserId() != null && !"".equals(filter.getUserId())) {
                query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
            }
        }
        return query.count();
    }

    /**
     * 获取已完成或未开工的项目数
     *
     * @param isStart     是否开工
     * @param projectType 项目类型，为null则不筛选
     * @return
     */
    public int getStarOrNotWorkNum(boolean isStart, String projectType) {
        GenericQuery query = createQuery("obj");
        if (isStart) {
            query.append(" and obj.status =:status").setParam("status", "4");
        } else {
            query.append(" and obj.status !=:status").setParam("status", "4");
        }
        if (ObjectUtils.isNotBlank(projectType)) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", projectType);
        }
        return query.count();
    }

    /**
     * 获取已完成或未开工的项目数
     *
     * @param isStart     是否开工
     * @param projectType 项目类型，为null则不筛选
     * @return
     */
    public int getStarOrNotWorkNumByUser(boolean isStart, String projectType, BnpUser user) {
        GenericQuery query = createQuery("obj");
        if (isStart) {
            query.append(" and obj.status =:status").setParam("status", "4");
        } else {
            query.append(" and obj.status !=:status").setParam("status", "4");
        }
        if ("6".equals(user.getBranchType())) {
            //县分管领导
            query.append(" and obj.resPhone =:resPhone").setParam("resPhone", user.getPhone());
        }
        if (ObjectUtils.isNotBlank(projectType)) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", projectType);
        }
        return query.count();
    }

    /**
     * 各行业项目数
     *
     * @return
     */
    public List<NewProjectsFilter> queryIndustryInfo() {
        StringBuffer str = new StringBuffer("");
        str.append(" SELECT COUNT(1) num,industry_type industryType");
        str.append(" FROM t_bnp_new_project");
        str.append(" GROUP BY industry_type");
        SQLQuery query = getSession().createSQLQuery(str.toString());
        query.setResultTransformer(Transformers.aliasToBean(NewProjectsFilter.class));
        List<NewProjectsFilter> list = query.list();
        return list;
    }

    /**
     * 按进度分类查询
     *
     * @return
     */
    public List<NewProjectsFilter> queryScheduleInfo() {
        StringBuffer str = new StringBuffer("");
        str.append(" SELECT COUNT(1) num,sched_code schedCode");
        str.append(" FROM t_bnp_new_project");
        str.append(" GROUP BY sched_code");
        SQLQuery query = getSession().createSQLQuery(str.toString());
        query.setResultTransformer(Transformers.aliasToBean(NewProjectsFilter.class));
        List<NewProjectsFilter> list = query.list();
        return list;
    }

    /**
     * 根据类型获取第一条项目ID
     *
     * @param projectType
     * @return
     */
    public String getFirstProjectIdByType(String projectType, String status) {
        GenericQuery query = createQuery("obj");
        if (!"1".equals(projectType)) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", projectType);
            if (ObjectUtils.isNotBlank(status)) {
                if ("4".equals(status)) {
                    query.append(" and obj.status =:status").setParam("status", "4");
                } else {
                    query.append(" and obj.status !=:status").setParam("status", "4");
                }
            }
        }
        NewProjects projects = query.firstResult();
        return projects.getSysId();
    }

    /**
     * 根据开工与否查询
     *
     * @param filter
     * @return
     */
    public List<NewProjects> getProjectInfo2(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (ObjectUtils.isNotBlank(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResDept())) {
            query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResUser())) {
            query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getNatureType())) {
            query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
        }
        if (ObjectUtils.isNotBlank(filter.getMoney())) {
            query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getStatus())) {
            if ("4".equals(filter.getStatus())) {
                query.append(" and obj.status =:status").setParam("status", "4");
            } else {
                query.append(" and obj.status !=:status").setParam("status", "4");
            }
        }
        if (ObjectUtils.isNotBlank(filter.getAddress())) {
            query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getDescribes())) {
            query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getContactName())) {
            query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getContactPhone())) {
            query.append(" and obj.contactPhone like:contactPhone").setParam("contactPhone", "%" + filter.getContactPhone() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getIndustryType())) {
            query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
        }

        if (ObjectUtils.isNotBlank(filter.getProjectType())) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
        }
        if (ObjectUtils.isNotBlank(filter.getSchedCode())) {
            query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
        }
        if (ObjectUtils.isNotBlank(filter.getSysId())) {
            query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
        }
        if (ObjectUtils.isNotBlank(filter.getUserId())) {
            query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
        }
        return query.listResult(filter);
    }

    //项目管理搜索功能
    public List<NewProjects> getProjectInfo4(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (ObjectUtils.isNotBlank(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResDept())) {
            query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResUser())) {
            query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
        }

        if (ObjectUtils.isNotBlank(filter.getContactName())) {
            query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
        }

        if (ObjectUtils.isNotBlank(filter.getContactPhone())) {
            if (ObjectUtils.isNotBlank(filter.getOccupyOperateUserPhone())) {
                query.append(" and (obj.contactPhone like:contactPhone").setParam(
                        "contactPhone", "%" + filter.getContactPhone() + "%");
                query.append(" or obj.occupyOperateUserPhone like:occupyOperateUserPhone )").setParam(
                        "occupyOperateUserPhone", "%" + filter.getOccupyOperateUserPhone() + "%");
            }
        }

        if (ObjectUtils.isNotBlank(filter.getResPhone())) {
            if (ObjectUtils.isNotBlank(filter.getOccupyResUserPhone())) {
                query.append(" and ( obj.resPhone =:resPhone").setParam(
                        "resPhone", filter.getResPhone());
                query.append(" or obj.occupyResUserPhone like:occupyResUserPhone )").setParam(
                        "occupyResUserPhone", "%" + filter.getOccupyResUserPhone() + "%");
            }
        }

        if (ObjectUtils.isNotBlank(filter.getNatureType())) {
            query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
        }
        if (ObjectUtils.isNotBlank(filter.getMoney())) {
            query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getCreateDate())) {
            query.append(" and obj.createDate like:createDate").setParam("createDate", "%" + filter.getCreateDate() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getStatus())) {
            //查询所有未开工项目
            if (!"4".equals(filter.getStatus())) {
                query.append(" and obj.status !=:status").setParam("status", "4");
            } else {
                query.append(" and obj.status =:status").setParam("status", filter.getStatus());
            }
        }

        if (StringUtils.isNotBlank(filter.getFlag())) {
            //预备项目
            if ("1,2".equals(filter.getFlag())) {
                List<String> list = new ArrayList<>();
                list.add("1");
                list.add("2");
                query.append(" and obj.status in (:status)").setParamList("status", list);
            }
            //前期项目
            if ("3,6".equals(filter.getFlag())) {
                List<String> list = new ArrayList<>();
                list.add("3");
                list.add("6");
                query.append(" and obj.status in (:status)").setParamList("status", list);
            }
            if ("1".equals(filter.getFlag())) {       //审批部门确认
                query.append(" and obj.status=:status").setParam("status", "1");
            }
            if ("2".equals(filter.getFlag())) {       //攻坚办审核
                query.append(" and obj.status=:status").setParam("status", "2");
            }
            if ("3".equals(filter.getFlag())) {       //项目节点进行中
                query.append(" and obj.status=:status").setParam("status", "3");
            }
            if ("4".equals(filter.getFlag())) {       //项目节点完成
                query.append(" and obj.status=:status").setParam("status", "4");
            }
            if ("5".equals(filter.getFlag())) {       //项目审核未通过
                query.append(" and obj.status=:status").setParam("status", "5");
            }
            if ("6".equals(filter.getFlag())) {       //项目节点暂停
                query.append(" and obj.status=:status").setParam("status", "6");
            }
        }

        if (ObjectUtils.isNotBlank(filter.getAddress())) {
            query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getDescribes())) {
            query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
        }

        if (ObjectUtils.isNotBlank(filter.getIndustryType())) {
            query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
        }
        //业主单位责任人手机号
        if (ObjectUtils.isNotBlank(filter.getOwnerUnitPersonPhone())) {
            query.append(" and obj.ownerUnitPersonPhone like:ownerUnitPersonPhone").setParam(
                    "ownerUnitPersonPhone", StringUtils.getLikewords(filter.getOwnerUnitPersonPhone()));
        }
        if (ObjectUtils.isNotBlank(filter.getProjectType())) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
        }
        if (ObjectUtils.isNotBlank(filter.getSchedCode())) {
            query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
        }
        if (ObjectUtils.isNotBlank(filter.getSysId())) {
            query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
        }
        if (ObjectUtils.isNotBlank(filter.getUserId())) {
            query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
        }
        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            query.append(" and (obj.name like:name or " +
                    "obj.contactName like:contactName or " +
                    "obj.contactPhone like:contactPhone or " +
                    "obj.resDept like:resDept or " +
                    "obj.resUser like:resUser) ").setParam("name",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("contactName",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("contactPhone",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resDept",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resUser",
                    StringUtils.getLikewords(filter.getSearchWord()));
        }

        if (!CollectionUtils.isEmpty(filter.getSysids())) {
            query.append(" and obj.sysId in (:sysId)").setParamList("sysId", filter.getSysids());
        }

        return query.listResult(filter);
    }


    //项目管理搜索功能
    public List<NewProjects> getProjectInfoNoPage(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (ObjectUtils.isNotBlank(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResDept())) {
            query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResUser())) {
            query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getResPhone())) {
            query.append(" and obj.resPhone =:resPhone").setParam("resPhone", filter.getResPhone());
        }
        if (ObjectUtils.isNotBlank(filter.getNatureType())) {
            query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
        }
        if (ObjectUtils.isNotBlank(filter.getMoney())) {
            query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getCreateDate())) {
            query.append(" and obj.createDate like:createDate").setParam("createDate", "%" + filter.getCreateDate() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getStatus())) {

            if (!"4".equals(filter.getStatus())) {
                //查询所有未开工项目
                query.append(" and obj.status !=:status").setParam("status", "4");
            } else {
                query.append(" and obj.status =:status").setParam("status", filter.getStatus());
            }
        }

        if (StringUtils.isNotBlank(filter.getFlag())) {
            //预备项目
            if ("1,2".equals(filter.getFlag())) {
                List<String> list = new ArrayList<>();
                list.add("1");
                list.add("2");
                query.append(" and obj.status in (:status)").setParamList("status", list);
            }
            //前期项目
            if ("3,6".equals(filter.getFlag())) {
                List<String> list = new ArrayList<>();
                list.add("3");
                list.add("6");
                query.append(" and obj.status in (:status)").setParamList("status", list);
            }
            if ("1".equals(filter.getFlag())) {       //审批部门确认
                query.append(" and obj.status=:status").setParam("status", "1");
            }
            if ("2".equals(filter.getFlag())) {       //攻坚办审核
                query.append(" and obj.status=:status").setParam("status", "2");
            }
            if ("3".equals(filter.getFlag())) {       //项目节点进行中
                query.append(" and obj.status=:status").setParam("status", "3");
            }
            if ("4".equals(filter.getFlag())) {       //项目节点完成
                query.append(" and obj.status=:status").setParam("status", "4");
            }
            if ("6".equals(filter.getFlag())) {       //项目节点暂停
                query.append(" and obj.status=:status").setParam("status", "6");
            }
        }

        if (ObjectUtils.isNotBlank(filter.getAddress())) {
            query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getDescribes())) {
            query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getContactName())) {
            query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
        }
        if (ObjectUtils.isNotBlank(filter.getContactPhone())) {
            query.append(" and obj.contactPhone =:contactPhone").setParam("contactPhone", filter.getContactPhone());
        }
        if (ObjectUtils.isNotBlank(filter.getIndustryType())) {
            query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
        }
        //业主单位责任人手机号
        if (ObjectUtils.isNotBlank(filter.getOwnerUnitPersonPhone())) {
            query.append(" and obj.ownerUnitPersonPhone like:ownerUnitPersonPhone").setParam(
                    "ownerUnitPersonPhone", StringUtils.getLikewords(filter.getOwnerUnitPersonPhone()));
        }
        if (ObjectUtils.isNotBlank(filter.getProjectType())) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
        }
        if (ObjectUtils.isNotBlank(filter.getSchedCode())) {
            query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
        }
        if (ObjectUtils.isNotBlank(filter.getSysId())) {
            query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
        }
        if (ObjectUtils.isNotBlank(filter.getUserId())) {
            query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
        }
        //模糊搜索
        if (StringUtils.isNotBlank(filter.getSearchWord())) {
            query.append(" and (obj.name like:name or " +
                    "obj.contactName like:contactName or " +
                    "obj.contactPhone like:contactPhone or " +
                    "obj.resDept like:resDept or " +
                    "obj.resUser like:resUser) ").setParam("name",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("contactName",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("contactPhone",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resDept",
                    StringUtils.getLikewords(filter.getSearchWord())).setParam("resUser",
                    StringUtils.getLikewords(filter.getSearchWord()));
        }
        return query.listResult();
    }


    public List<NewProjects> getFinishProject() {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.status = :status").setParam("status", "4");
        return query.listResult();
    }

    public List<NewProjects> getProjectsInfo(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (filter.getName() != null && !"".equals(filter.getName())) {
            query.append(" and obj.name like:name").setParam("name", "%" + filter.getName() + "%");
        }
        if (filter.getResDept() != null && !"".equals(filter.getResDept())) {
            query.append(" and obj.resDept like:resDept").setParam("resDept", "%" + filter.getResDept() + "%");
        }
        if (filter.getResUser() != null && !"".equals(filter.getResUser())) {
            query.append(" and obj.resUser like:resUser").setParam("resUser", "%" + filter.getResUser() + "%");
        }
        if (filter.getNatureType() != null && !"".equals(filter.getNatureType())) {
            query.append(" and obj.natureType =:natureType").setParam("natureType", filter.getNatureType());
        }
        if (filter.getMoney() != null && !"".equals(filter.getMoney())) {
            query.append(" and obj.money like:money").setParam("money", "%" + filter.getMoney() + "%");
        }
        if (filter.getStatus() != null && !"".equals(filter.getStatus())) {
            query.append(" and obj.status =:status").setParam("status", filter.getStatus());
        }
        if (filter.getAddress() != null && !"".equals(filter.getAddress())) {
            query.append(" and obj.address like:address").setParam("address", "%" + filter.getAddress() + "%");
        }
        if (filter.getDescribes() != null && !"".equals(filter.getDescribes())) {
            query.append(" and obj.describes like:describes").setParam("describes", "%" + filter.getDescribes() + "%");
        }
        if (filter.getContactName() != null && !"".equals(filter.getContactName())) {
            query.append(" and obj.contactName like:contactName").setParam("contactName", "%" + filter.getContactName() + "%");
        }
        if (filter.getContactPhone() != null && !"".equals(filter.getContactPhone())) {
            query.append(" and obj.contactPhone like:contactPhone").setParam("contactPhone", "%" + filter.getContactPhone() + "%");
        }
        if (filter.getIndustryType() != null && !"".equals(filter.getIndustryType())) {
            query.append(" and obj.industryType =:industryType").setParam("industryType", filter.getIndustryType());
        }
        if (filter.getProjectType() != null && !"".equals(filter.getProjectType())) {
            query.append(" and obj.projectType =:projectType").setParam("projectType", filter.getProjectType());
        }
        if (filter.getSchedCode() != null && !"".equals(filter.getSchedCode())) {
            query.append(" and obj.schedCode =:schedCode").setParam("schedCode", filter.getSchedCode());
        }
        if (filter.getSysId() != null && !"".equals(filter.getSysId())) {
            query.append(" and obj.sysId =:sysId").setParam("sysId", filter.getSysId());
        }
        if (filter.getUserId() != null && !"".equals(filter.getUserId())) {
            query.append(" and obj.userId =:userId").setParam("userId", filter.getUserId());
        }
        query.append(" order by obj.sysId DESC");

        return query.listResult();
    }

    public int getProjectsByNatureTypeDetail(String natureTypeDetail) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.natureTypeDetail =:natureTypeDetail").setParam("natureTypeDetail", natureTypeDetail);
        return query.listResult().size();
    }

    public List<String> getResDpetInAllProject() {
        StringBuffer str = new StringBuffer();
        str.append(" select res_dept as resDept from t_bnp_new_project group by res_dept");
        SQLQuery query = getSession().createSQLQuery(str.toString());
        query.setResultTransformer(Transformers.aliasToBean(NewProjects.class));
        List<NewProjects> list = query.list();
        List<String> result = new ArrayList<>();
        for (NewProjects p : list) {
            if (StringUtils.isNotBlank(p.getResDept())) {
                result.add(p.getResDept());
            }
        }
        return result;
    }

    public List<NewProjects> queryStartScheduleByDataTime(NewProjectsFilter filter) {
        GenericQuery query = createQuery("obj");
        if (filter.getDataTime() != null && !"".equals(filter.getDataTime())) {
            query.append(" and substr(obj.factFinishDate,1,7) =:dataTime " +
                    "or substr(obj.startTime,1,7) =:dataTime")
                    .setParam("dataTime", filter.getDataTime());
        }
        return query.listResult();
    }
}
